/**
 * Скрипт собирает статистику по количеству оформленных в ИС заказов за месяц
 */

import groovy.sql.Sql

def goodwill = Sql.newInstance("jdbc:mysql://87.245.178.50/goodwill-store", "goodwill-store", "6533xx", "com.mysql.jdbc.Driver")

def year = '2010'
def month = '10'

def ORDERS_BY_MONTH = "select COUNT(o.Id) from Orders o where o.`1CNum` is not null and o.CreationDate BETWEEN ${year + '-' + month + '-01'} and ${year + '-' + month + '-31'}"
def ORDERS_BY_M1 = "select COUNT(o.Id) from Orders o where o.`1CNum` is not null and o.CreationDate BETWEEN ${year + '-' + month + '-01'} and ${year + '-' + month + '-07'}"
def ORDERS_BY_M2 = "select COUNT(o.Id) from Orders o where o.`1CNum` is not null and o.CreationDate BETWEEN ${year + '-' + month + '-08'} and ${year + '-' + month + '-14'}"
def ORDERS_BY_M3 = "select COUNT(o.Id) from Orders o where o.`1CNum` is not null and o.CreationDate BETWEEN ${year + '-' + month + '-15'} and ${year + '-' + month + '-21'}"
def ORDERS_BY_M4 = "select COUNT(o.Id) from Orders o where o.`1CNum` is not null and o.CreationDate BETWEEN ${year + '-' + month + '-22'} and ${year + '-' + month + '-31'}"

def ORDERS_SUM = "select sum(oi.Count * GREATEST(oi.StartPrice, oi.ManualPrice)) from Orders o, OrderItems oi where oi.IdOrder = o.Id and o.CreationDate BETWEEN ${year + '-' + month + '-01'} and ${year + '-' + month + '-31'}"
def ORDERS_SUM_DAY = "select sum(oi.Count * GREATEST(oi.StartPrice, oi.ManualPrice))/20 from Orders o, OrderItems oi where oi.IdOrder = o.Id and o.CreationDate BETWEEN ${year + '-' + month + '-01'} and ${year + '-' + month + '-31'}"


goodwill.eachRow(ORDERS_BY_MONTH, {
  row -> println "Всего за месяц: ${row[0]}"
})

goodwill.eachRow(ORDERS_BY_M1, {
  row -> println "За первую неделю: ${row[0]}"
})
goodwill.eachRow(ORDERS_BY_M2, {
  row -> println "За вторую неделю: ${row[0]}"
})
goodwill.eachRow(ORDERS_BY_M3, {
  row -> println "За третью неделю: ${row[0]}"
})
goodwill.eachRow(ORDERS_BY_M4, {
  row -> println "За четвертую неделю: ${row[0]}"
})

goodwill.eachRow(ORDERS_SUM, {
  row -> println "Сумма заказов за месяц: ${row[0]}"
})
goodwill.eachRow(ORDERS_SUM_DAY, {
  row -> println "Средняя сумма в день: ${row[0]}"
})
